
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;
using AnovoAndes.Framework.Utilidades;
using NovaSystem.EntidadesNegocio;

namespace NovaSystem.AccesoDatos
{
	public class clienteDAO 
	{
			
		#region [Variables Publicas]
        List<string> listaEsquema = new List<string>();
		System.Configuration.AppSettingsReader read = new System.Configuration.AppSettingsReader();
        #endregion
		
		#region Constructores
		public clienteDAO()
		{
		}
		#endregion
		
		#region Metodos Privados
		
		 private List<string> LlenarEsquema(IDataReader dr)
        {
            DataTable schemaTable;
            schemaTable = dr.GetSchemaTable();
            listaEsquema = new List<string>();
            const int columnaNombre = 0;
            //Para cada campo de la tabla...
            foreach (DataRow myField in schemaTable.Rows)
            {
                //Agrega nombre del campo a la lista generica.
                listaEsquema.Add(myField[columnaNombre].ToString());
            }
            return listaEsquema;

        }
		
		private cliente Crear(IDataReader dr)
		{
			cliente vObj = new cliente();
			try
			{
				listaEsquema.Sort();
							if (listaEsquema.BinarySearch("idCliente",StringComparer.OrdinalIgnoreCase)>-1)
				{
					if(!(dr["idCliente"] is System.DBNull))
					{
						vObj.idCliente= (int)dr["idCliente"];
					}
				}
				if (listaEsquema.BinarySearch("rutCliente",StringComparer.OrdinalIgnoreCase)>-1)
				{
					if(!(dr["rutCliente"] is System.DBNull))
					{
						vObj.rutCliente= (string)dr["rutCliente"];
					}
				}
				if (listaEsquema.BinarySearch("nombreCliente",StringComparer.OrdinalIgnoreCase)>-1)
				{
					if(!(dr["nombreCliente"] is System.DBNull))
					{
						vObj.nombreCliente= (string)dr["nombreCliente"];
					}
				}
				if (listaEsquema.BinarySearch("direccionCliente",StringComparer.OrdinalIgnoreCase)>-1)
				{
					if(!(dr["direccionCliente"] is System.DBNull))
					{
						vObj.direccionCliente= (string)dr["direccionCliente"];
					}
				}
				if (listaEsquema.BinarySearch("conceptoPais",StringComparer.OrdinalIgnoreCase)>-1)
				{
					if(!(dr["conceptoPais"] is System.DBNull))
					{
						vObj.conceptoPais= (string)dr["conceptoPais"];
					}
				}
				if (listaEsquema.BinarySearch("codigoPais",StringComparer.OrdinalIgnoreCase)>-1)
				{
					if(!(dr["codigoPais"] is System.DBNull))
					{
						vObj.codigoPais= (string)dr["codigoPais"];
					}
				}
				if (listaEsquema.BinarySearch("Ciudad",StringComparer.OrdinalIgnoreCase)>-1)
				{
					if(!(dr["Ciudad"] is System.DBNull))
					{
						vObj.Ciudad= (string)dr["Ciudad"];
					}
				}
				if (listaEsquema.BinarySearch("Provincia",StringComparer.OrdinalIgnoreCase)>-1)
				{
					if(!(dr["Provincia"] is System.DBNull))
					{
						vObj.Provincia= (string)dr["Provincia"];
					}
				}
				if (listaEsquema.BinarySearch("contacto",StringComparer.OrdinalIgnoreCase)>-1)
				{
					if(!(dr["contacto"] is System.DBNull))
					{
						vObj.contacto= (string)dr["contacto"];
					}
				}
				if (listaEsquema.BinarySearch("telefono",StringComparer.OrdinalIgnoreCase)>-1)
				{
					if(!(dr["telefono"] is System.DBNull))
					{
						vObj.telefono= (string)dr["telefono"];
					}
				}
				if (listaEsquema.BinarySearch("email",StringComparer.OrdinalIgnoreCase)>-1)
				{
					if(!(dr["email"] is System.DBNull))
					{
						vObj.email= (string)dr["email"];
					}
				}
				if (listaEsquema.BinarySearch("movil",StringComparer.OrdinalIgnoreCase)>-1)
				{
					if(!(dr["movil"] is System.DBNull))
					{
						vObj.movil= (string)dr["movil"];
					}
				}

				return vObj;
			}
			catch(Exception ex)
			{
				throw new Exception(ex.Message);
			}
		}
		
		private List<cliente> CrearDTOs(IDataReader dr) 
		{
			List<cliente> listaDTO = new List<cliente>();
			if 	(dr != null)
			{
				listaEsquema = LlenarEsquema(dr);// Validadores.LlenarEsquema(dr);
				
				while (dr.Read())
				{
					listaDTO.Add(Crear(dr));
				}
				dr.Close();
			}
			return listaDTO;
		}
		
		#endregion
		
		#region Metodos Publicos
        public cliente BuscarRut(string rut)
        {
            MySqlConnection conn = new MySqlConnection();
            List<cliente> lista = new List<cliente>();
            try
            {
                string connString = read.GetValue("Database", typeof(string)).ToString();
                conn.ConnectionString = connString;
                conn.Open();
                MySqlCommand objDbCommand = new MySqlCommand();
                objDbCommand.Connection = conn;
                objDbCommand.CommandType = CommandType.StoredProcedure;
                objDbCommand.CommandText = "usp_S_buscaClienteRut";
                objDbCommand.Parameters.AddWithValue("_rutCliente", rut);
                using (IDataReader objDataReader = objDbCommand.ExecuteReader())
                {
                    lista = CrearDTOs(objDataReader);
                }
                CerrarConn(conn);
                if (lista.Count > 0)
                    return lista[0];
                else
                    return null;
            }
            catch (Exception ex)
            {
                CerrarConn(conn);
                throw new Exception(ex.Message);
            }
        }


        public List<cliente> Buscar(cliente dto)
        {
            MySqlConnection conn = new MySqlConnection();
            List<cliente> lista = new List<cliente>();
            try
            {
                string connString = read.GetValue("Database", typeof(string)).ToString();
                conn.ConnectionString = connString;
                conn.Open();
                MySqlCommand objDbCommand = new MySqlCommand();
                objDbCommand.Connection = conn;
                objDbCommand.CommandType = CommandType.StoredProcedure;
                objDbCommand.CommandText = "usp_S_buscaCliente";
                objDbCommand.Parameters.AddWithValue("_rutCliente", AssertNull.getValue(dto.rutCliente));
                objDbCommand.Parameters.AddWithValue("_nombreCliente", AssertNull.getValue(dto.nombreCliente));
                objDbCommand.Parameters.AddWithValue("_codigoPais", AssertNull.getValue(dto.codigoPais));
                objDbCommand.Parameters.AddWithValue("_contacto", AssertNull.getValue(dto.contacto));
                objDbCommand.Parameters.AddWithValue("_email", AssertNull.getValue(dto.email));
                using (IDataReader objDataReader = objDbCommand.ExecuteReader())
                {
                    lista = CrearDTOs(objDataReader);
                }
                CerrarConn(conn);
                return lista;
            }
            catch (Exception ex)
            {
                CerrarConn(conn);
                throw new Exception(ex.Message);
            }
        }
		public List<cliente> Listar()
		{
			MySqlConnection conn = new MySqlConnection();
			List<cliente> lista = new List<cliente>();
			try
			{
				string connString = read.GetValue("Database",typeof(string)).ToString();
				conn.ConnectionString = connString;
				conn.Open();
				MySqlCommand objDbCommand = new MySqlCommand();
				objDbCommand.Connection = conn;
				objDbCommand.CommandType = CommandType.StoredProcedure;
				objDbCommand.CommandText = "usp_s_cliente";
				
				using (IDataReader objDataReader = objDbCommand.ExecuteReader())
				{
					lista = CrearDTOs(objDataReader);
				}
				CerrarConn(conn);
				return lista;
			}
			catch(Exception ex)
			{
				CerrarConn(conn);
				throw new Exception(ex.Message);
			}
		}
		
		
		private void CerrarConn(MySqlConnection conn)
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
                conn.Dispose();
            }
        }
		
		public int Insertar( cliente dto)
		{
			MySqlConnection conn = new MySqlConnection();
			try
			{
				string connString = read.GetValue("Database",typeof(string)).ToString();
				conn.ConnectionString = connString;
				conn.Open();
				MySqlCommand objDbCommand = new MySqlCommand();
				objDbCommand.Connection = conn;
				objDbCommand.CommandType = CommandType.StoredProcedure;
				objDbCommand.CommandText = "usp_I_cliente";
				objDbCommand.Parameters.AddWithValue("_idCliente",AssertNull.getValue(dto.idCliente));
				objDbCommand.Parameters.AddWithValue("_rutCliente",AssertNull.getValue(dto.rutCliente));
				objDbCommand.Parameters.AddWithValue("_nombreCliente",AssertNull.getValue(dto.nombreCliente));
				objDbCommand.Parameters.AddWithValue("_direccionCliente",AssertNull.getValue(dto.direccionCliente));
				objDbCommand.Parameters.AddWithValue("_conceptoPais",AssertNull.getValue(dto.conceptoPais));
				objDbCommand.Parameters.AddWithValue("_codigoPais",AssertNull.getValue(dto.codigoPais));
				objDbCommand.Parameters.AddWithValue("_Ciudad",AssertNull.getValue(dto.Ciudad));
				objDbCommand.Parameters.AddWithValue("_Provincia",AssertNull.getValue(dto.Provincia));
				objDbCommand.Parameters.AddWithValue("_contacto",AssertNull.getValue(dto.contacto));
				objDbCommand.Parameters.AddWithValue("_telefono",AssertNull.getValue(dto.telefono));
				objDbCommand.Parameters.AddWithValue("_email",AssertNull.getValue(dto.email));
				objDbCommand.Parameters.AddWithValue("_movil",AssertNull.getValue(dto.movil));
                objDbCommand.Parameters["_idCliente"].Direction = ParameterDirection.Output;
                int retorno = 0;
                int.TryParse(objDbCommand.ExecuteScalar().ToString(), out retorno);
				CerrarConn(conn);
				return retorno;
			}
			catch(Exception ex)
			{
				CerrarConn(conn);
				throw new Exception(ex.Message);
			}
		}
		
		public void Eliminar(cliente dto)
		{
			MySqlConnection conn = new MySqlConnection();
			try
			{
				string connString = read.GetValue("Database",typeof(string)).ToString();
				conn.ConnectionString = connString;
				conn.Open();
				MySqlCommand objDbCommand = new MySqlCommand();
				objDbCommand.Connection = conn;
				objDbCommand.CommandType = CommandType.StoredProcedure;
				objDbCommand.CommandText = "usp_D_cliente";
								objDbCommand.Parameters.AddWithValue("_idCliente",AssertNull.getValue(dto.idCliente));

				objDbCommand.ExecuteNonQuery();
				CerrarConn(conn);
			}
			catch(Exception ex)
			{
				CerrarConn(conn);
				throw new Exception(ex.Message);
			}
		}
		
		#endregion
		
	}
}
